package com.stable.model;

import com.stable.spider.eastmoney.EastMoneySpider;
import com.stable.spider.ths.XLSData;
import com.stable.vo.BonusFinancing;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Font;

import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

//PE三阶段估值模型变身
public class PE3change {

    public static void PE3changeWriteXls(String code, HSSFWorkbook workbook, List<XLSData> benefitList, List<XLSData> cashList, List<XLSData> debtList, List<XLSData> mainList) {
        HSSFSheet sheet = workbook.createSheet("PE三阶段估值模型变身");// 创建工作表(Sheet)
        List nullList = Arrays.asList();

        HSSFUtil.row(workbook, sheet, 0, code + ":报告参数", "科目\\时间", benefitList);
        HSSFUtil.row(workbook, sheet, 1, "净利润:NI", "净利润(元)", mainList);

        HSSFUtil.rowFormula(workbook, sheet, 2, "净利润：亿", "CS2/100000000", benefitList, "0.00");

        EastMoneySpider eastMoneySpider = new EastMoneySpider();
        Map<String, BonusFinancing> bfMap = eastMoneySpider.getBonusFinancing(code).stream().collect(Collectors.toMap(BonusFinancing::getSj, bf -> bf, (oldBf, newBf) -> newBf));
        benefitList.stream().filter(xlsData -> StringUtils.equals(xlsData.getKey(), "科目\\时间")).forEach(xlsData -> {
            HSSFRow row4 = sheet.createRow(4);
            HSSFUtil.rowColumn(workbook, row4, 0, "分红明细", Font.COLOR_NORMAL);
            if (0 != xlsData.getYData1()) {
                HSSFUtil.rowColumn(workbook, row4, 1, bfMap.get(new Double(xlsData.getYData1()).intValue() +"") == null ? "0" : bfMap.get(new Double(xlsData.getYData1()).intValue() +"").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData2()) {
                HSSFUtil.rowColumn(workbook, row4, 2, bfMap.get(new Double(xlsData.getYData2()).intValue() +"") == null ? "0" : bfMap.get(new Double(xlsData.getYData2()).intValue() +"").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData3()) {
                HSSFUtil.rowColumn(workbook, row4, 3, bfMap.get(new Double(xlsData.getYData3()).intValue() +"") == null ? "0" : bfMap.get(new Double(xlsData.getYData3()).intValue() +"").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData4()) {
                HSSFUtil.rowColumn(workbook, row4, 4, bfMap.get(new Double(xlsData.getYData4()).intValue() +"") == null ? "0" : bfMap.get(new Double(xlsData.getYData4()).intValue() +"").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData5()) {
                HSSFUtil.rowColumn(workbook, row4, 5, bfMap.get(new Double(xlsData.getYData5()).intValue() +"") == null ? "0" : bfMap.get(new Double(xlsData.getYData5()).intValue() +"").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData6()) {
                HSSFUtil.rowColumn(workbook, row4, 6, bfMap.get(new Double(xlsData.getYData6()).intValue() +"") == null ? "0" : bfMap.get(new Double(xlsData.getYData6()).intValue() +"").getMgsy(), Font.COLOR_NORMAL);
            }
        });

        HSSFRow row5 = sheet.createRow(5);
        HSSFUtil.rowColumn(workbook, row5, 0, "分红：亿", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row5, 1, "B5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 2, "C5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 3, "D5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 4, "E5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 5, "F5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 6, "G5/10000", Font.COLOR_NORMAL, "0.00");


        HSSFRow row6 = sheet.createRow(6);
        HSSFUtil.rowColumn(workbook, row6, 0, "分红率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row6, 1, "B6/B3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 2, "C6/C3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 3, "D6/D3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 4, "E6/E3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 5, "F6/F3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 6, "G6/G3", Font.COLOR_NORMAL, "0.00%");

        HSSFUtil.row(workbook, sheet, 7, "加权净资产收益率", "净资产收益率", mainList);
        HSSFUtil.row(workbook, sheet, 8, "净利润同比增长率", "净利润同比增长率", mainList);
        HSSFUtil.row(workbook, sheet, 9, "扣非净利润同比增长率", "扣非净利润同比增长率", mainList);



        HSSFRow row11 = sheet.createRow(11);
        HSSFUtil.rowColumn(workbook, row11, 0, "平均值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row11, 1, "利润增长率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row11, 2, "归母净利润", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row11, 3, "分红率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row11, 4, "ROE", Font.COLOR_NORMAL);

        HSSFRow row12 = sheet.createRow(12);
        HSSFUtil.rowColumn(workbook, row12, 0, "5年平均", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row12, 1, "AVERAGE(B9:F9)/100", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row12, 3, "AVERAGE(C7:G7)", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row12, 4, "AVERAGE(B8:F8)/100", Font.COLOR_RED, "0.0%");


        HSSFRow row13 = sheet.createRow(13);
        HSSFUtil.rowColumn(workbook, row13, 0, "10年平均", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row13, 1, "AVERAGE(B9:K9)/100", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row13, 3, "AVERAGE(C7:G7)", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row13, 4, "AVERAGE(B8:K8)/100", Font.COLOR_RED, "0.0%");



        HSSFRow row15 = sheet.createRow(15);
        HSSFUtil.rowColumn(workbook, row15, 0, "1、股东价值-PE三阶段估值模型（默认过去5年）", Font.COLOR_NORMAL);

        HSSFRow row16 = sheet.createRow(16);
        HSSFUtil.rowColumn(workbook, row16, 0, "计算模型", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 1, "利润增长率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 2, "归母净利润", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 3, "分红率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 4, "ROE", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 5, "股东价值率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 6, "股东价值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 7, "折现率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 8, "折现系数", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 9, "股东价值折现", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 10, "股东价值折现：累计求和", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 11, "股东价值折现求和", Font.COLOR_NORMAL);

        HSSFRow row17 = sheet.createRow(17);
        HSSFUtil.rowColumn(workbook, row17, 0, "今年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row17, 2, "1", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row17, 4, "B8/100", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumn(workbook, row17, 7, "10%", Font.COLOR_RED);
        //rowColumn(workbook, row17, 9, "1-10年", Font.COLOR_NORMAL);
        //rowColumnFormula(workbook, row17, 10, "(1+B18)*F18/(G18-B18)-((1+B18)^11)*F18/(G18-B18)/(1+G18)^10", Font.COLOR_RED, "0.0");


        HSSFRow row18 = sheet.createRow(18);
        HSSFUtil.rowColumn(workbook, row18, 0, "未来10年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row18, 1, "AVERAGE(B9:F9)/100", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row18, 3, "AVERAGE(C7:G7)", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row18, 4, "AVERAGE(B8:F8)/100", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumn(workbook, row18, 11, "1-10年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row18, 12, "SUM(J20:J29)", Font.COLOR_RED, "0.0");

        HSSFRow row19 = sheet.createRow(19);
        HSSFUtil.rowColumn(workbook, row19, 0, "第1年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row19, 1, "B$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row19, 2, "C18*(1+B20)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row19, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row19, 4, "B20/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row19, 5, "D20+(1-D20)*E20", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row19, 6, "C20*F20", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row19, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row19, 8, "1/(1+H20)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row19, 9, "G20*I20", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row19, 10, "SUM(J20:J20)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row19, 11, "11-20年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row19, 12, "SUM(J31:J40)", Font.COLOR_RED, "0.0");

        HSSFRow row20 = sheet.createRow(20);
        HSSFUtil.rowColumn(workbook, row20, 0, "第2年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row20, 1, "B$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row20, 2, "C20*(1+B21)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row20, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row20, 4, "B21/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row20, 5, "D21+(1-D21)*E21", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row20, 6, "C21*F21", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row20, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row20, 8, "I20/(1+H21)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row20, 9, "G21*I21", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row20, 10, "SUM(J20:J21)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row20, 11, "20年后", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row20, 12, "G42/(H42-B42)*I40", Font.COLOR_RED, "0.0");

        HSSFRow row21 = sheet.createRow(21);
        HSSFUtil.rowColumn(workbook, row21, 0, "第3年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row21, 1, "B$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row21, 2, "C21*(1+B22)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row21, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row21, 4, "B22/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row21, 5, "D22+(1-D22)*E22", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row21, 6, "C22*F22", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row21, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row21, 8, "I21/(1+H22)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row21, 9, "G22*I22", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row21, 10, "SUM(J20:J22)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row21, 11, "三部分相加", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row21, 12, "M19+M20+M21", Font.COLOR_RED, "0.0");

        HSSFRow row22 = sheet.createRow(22);
        HSSFUtil.rowColumn(workbook, row22, 0, "第4年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row22, 1, "B$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row22, 2, "C22*(1+B23)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row22, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row22, 4, "B23/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row22, 5, "D23+(1-D23)*E23", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row22, 6, "C23*F23", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row22, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row22, 8, "I22/(1+H23)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row22, 9, "G23*I23", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row22, 10, "SUM(J20:J23)", Font.COLOR_NORMAL, "0.00");


        HSSFRow row23 = sheet.createRow(23);
        HSSFUtil.rowColumn(workbook, row23, 0, "第5年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row23, 1, "B$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row23, 2, "C23*(1+B24)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row23, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row23, 4, "B24/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row23, 5, "D24+(1-D24)*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row23, 6, "C24*F24", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row23, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row23, 8, "I23/(1+H24)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row23, 9, "G24*I24", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row23, 10, "SUM(J20:J24)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row23, 11, "PE估值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row23, 12, "M22", Font.COLOR_RED, "0.0");

        HSSFRow row24 = sheet.createRow(24);
        HSSFUtil.rowColumn(workbook, row24, 0, "第6年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row24, 1, "B$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 2, "C24*(1+B25)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row24, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 4, "B25/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 5, "D25+(1-D25)*E25", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 6, "C25*F25", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row24, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 8, "I24/(1+H25)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row24, 9, "G25*I25", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row24, 10, "SUM(J20:J25)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row24, 11, "PB估值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row24, 12, "M24*E18", Font.COLOR_RED, "0.0");

        HSSFRow row25 = sheet.createRow(25);
        HSSFUtil.rowColumn(workbook, row25, 0, "第7年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row25, 1, "B$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 2, "C25*(1+B26)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row25, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 4, "B26/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 5, "D26+(1-D26)*E26", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 6, "C26*F26", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row25, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 8, "I25/(1+H26)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row25, 9, "G26*I26", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row25, 10, "SUM(J20:J26)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row26 = sheet.createRow(26);
        HSSFUtil.rowColumn(workbook, row26, 0, "第8年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row26, 1, "B$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 2, "C26*(1+B27)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row26, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 4, "B27/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 5, "D27+(1-D27)*E27", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 6, "C27*F27", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row26, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 8, "I26/(1+H27)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row26, 9, "G27*I27", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row26, 10, "SUM(J20:J27)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row27 = sheet.createRow(27);
        HSSFUtil.rowColumn(workbook, row27, 0, "第9年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row27, 1, "B$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 2, "C27*(1+B28)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row27, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 4, "B28/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 5, "D28+(1-D28)*E28", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 6, "C28*F28", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row27, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 8, "I27/(1+H28)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row27, 9, "G28*I28", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row27, 10, "SUM(J20:J28)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row28 = sheet.createRow(28);
        HSSFUtil.rowColumn(workbook, row28, 0, "第10年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row28, 1, "B$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row28, 2, "C28*(1+B29)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row28, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row28, 4, "B29/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row28, 5, "D29+(1-D29)*E29", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row28, 6, "C29*F29", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row28, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row28, 8, "I28/(1+H29)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row28, 9, "G29*I29", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row28, 10, "SUM(J20:J29)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row29 = sheet.createRow(29);
        HSSFUtil.rowColumn(workbook, row29, 0, "10-20年", Font.COLOR_RED);

        HSSFRow row30 = sheet.createRow(30);
        HSSFUtil.rowColumn(workbook, row30, 0, "第11年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row30, 1, "B$19-(B$19-B$41)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row30, 2, "C29*(1+B31)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row30, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row30, 4, "B31/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row30, 5, "D31+(1-D31)*E31", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row30, 6, "C31*F31", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row30, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row30, 8, "I29/(1+H31)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row30, 9, "G31*I31", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row30, 10, "SUM(J20:J31)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row31 = sheet.createRow(31);
        HSSFUtil.rowColumn(workbook, row31, 0, "第12年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row31, 1, "B31-(B$19-B$41)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row31, 2, "C31*(1+B32)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row31, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row31, 4, "B32/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row31, 5, "D32+(1-D32)*E32", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row31, 6, "C32*F32", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row31, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row31, 8, "I31/(1+H32)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row31, 9, "G32*I32", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row31, 10, "SUM(J20:J32)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row32 = sheet.createRow(32);
        HSSFUtil.rowColumn(workbook, row32, 0, "第13年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row32, 1, "B32-(B$19-B$41)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row32, 2, "C32*(1+B33)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row32, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row32, 4, "B33/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row32, 5, "D33+(1-D33)*E33", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row32, 6, "C33*F33", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row32, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row32, 8, "I32/(1+H33)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row32, 9, "G33*I33", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row32, 10, "SUM(J20:J33)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row33 = sheet.createRow(33);
        HSSFUtil.rowColumn(workbook, row33, 0, "第14年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row33, 1, "B33-(B$19-B$41)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row33, 2, "C33*(1+B34)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row33, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row33, 4, "B34/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row33, 5, "D34+(1-D34)*E34", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row33, 6, "C34*F34", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row33, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row33, 8, "I33/(1+H34)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row33, 9, "G34*I34", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row33, 10, "SUM(J20:J34)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row34 = sheet.createRow(34);
        HSSFUtil.rowColumn(workbook, row34, 0, "第15年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row34, 1, "B34-(B$19-B$41)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row34, 2, "C34*(1+B35)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row34, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row34, 4, "B35/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row34, 5, "D35+(1-D35)*E35", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row34, 6, "C35*F35", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row34, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row34, 8, "I34/(1+H35)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row34, 9, "G35*I35", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row34, 10, "SUM(J20:J35)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row35 = sheet.createRow(35);
        HSSFUtil.rowColumn(workbook, row35, 0, "第16年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row35, 1, "B35-(B$19-B$41)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row35, 2, "C35*(1+B36)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row35, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row35, 4, "B36/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row35, 5, "D36+(1-D36)*E36", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row35, 6, "C36*F36", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row35, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row35, 8, "I35/(1+H36)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row35, 9, "G36*I36", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row35, 10, "SUM(J20:J36)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row36 = sheet.createRow(36);
        HSSFUtil.rowColumn(workbook, row36, 0, "第17年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row36, 1, "B36-(B$19-B$41)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row36, 2, "C36*(1+B37)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row36, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row36, 4, "B37/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row36, 5, "D37+(1-D37)*E37", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row36, 6, "C37*F37", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row36, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row36, 8, "I36/(1+H37)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row36, 9, "G37*I37", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row36, 10, "SUM(J20:J37)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row37 = sheet.createRow(37);
        HSSFUtil.rowColumn(workbook, row37, 0, "第18年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row37, 1, "B37-(B$19-B$41)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row37, 2, "C37*(1+B38)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row37, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row37, 4, "B38/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row37, 5, "D38+(1-D38)*E38", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row37, 6, "C38*F38", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row37, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row37, 8, "I37/(1+H38)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row37, 9, "G38*I38", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row37, 10, "SUM(J20:J38)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row38 = sheet.createRow(38);
        HSSFUtil.rowColumn(workbook, row38, 0, "第19年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row38, 1, "B38-(B$19-B$41)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row38, 2, "C38*(1+B39)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row38, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row38, 4, "B39/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row38, 5, "D39+(1-D39)*E39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row38, 6, "C39*F39", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row38, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row38, 8, "I38/(1+H39)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row38, 9, "G39*I39", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row38, 10, "SUM(J20:J39)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row39 = sheet.createRow(39);
        HSSFUtil.rowColumn(workbook, row39, 0, "第20年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row39, 1, "B39-(B$19-B$41)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row39, 2, "C39*(1+B40)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row39, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row39, 4, "B40/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row39, 5, "D40+(1-D40)*E40", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row39, 6, "C40*F40", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row39, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row39, 8, "I39/(1+H40)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row39, 9, "G40*I40", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row39, 10, "SUM(J20:J40)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row40 = sheet.createRow(40);
        HSSFUtil.rowColumn(workbook, row40, 0, "20年后", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row40, 1, "2%", Font.COLOR_RED);

        HSSFRow row41 = sheet.createRow(41);
        HSSFUtil.rowColumn(workbook, row41, 0, "第21年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row41, 1, "B$41", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row41, 2, "C40*(1+B42)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row41, 3, "D$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row41, 4, "B42/B$19*E$19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row41, 5, "D42+(1-D42)*E42", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row41, 6, "C42*F42", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row41, 7, "H$18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row41, 8, "I40/(1+H42)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row41, 9, "G42*I42", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row41, 10, "SUM(J20:J42)", Font.COLOR_NORMAL, "0.00");

    }
}
